package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import CONEXAO.Conexao;
import CORE.GenericDAO;
import DTO.ObraDTO;
import DTO.StatusDTO;

public class StatusDAO extends GenericDAO<StatusDTO> {
	
	public StatusDAO() {
		super("status", StatusDTO.class); 
	}	
	
	public StatusDTO pesquisarPorDescricao(StatusDTO statusDTO){
		try {
			StatusDTO status = null;
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);
            sql.append(" WHERE descricao = '(?)';");
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setString(1, statusDTO.getDescricao());
            
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
            	status = criarObjeto(rs);
            }
                        
            rs.close();
            ps.close();
            
            conn.close();
            
            return status;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public StatusDTO pesquisarStatusMaisFrequenteDaObra(ObraDTO obra){
		try {			
			StatusDTO status = new StatusDTO();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT status ");
            sql.append("FROM lista ");
            sql.append("WHERE obra = (?) ");
            sql.append("GROUP BY status ");
            sql.append("ORDER BY COUNT(status) ");
            sql.append("LIMIT 1");
                                    
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setLong(1, obra.getCodigo());
            
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
            	status = pesquisarPorCodigo(rs.getLong(1));
            }
                        
            rs.close();                        
            conn.close();
            
            return status;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean existe(StatusDTO statusDTO){
		try {
			return pesquisarPorDescricao(statusDTO) != null;
		} catch (Exception e) {
			return false;
		}
	}
	
	@Override
	public StatusDTO criarObjeto(ResultSet rs) throws SQLException {
		StatusDTO status = new StatusDTO();
		status.setCodigo(rs.getLong(1));
		status.setDescricao(rs.getString(2));		
		return status;
	}	
}